/**
 * 在这里定义和读者相关的路由处理函数，供 /router/readers.js 模块进行调用
 */

//导入数据库操作模块：
const { result } = require('@hapi/joi/lib/base')
const db = require('../db/index')

// 查询所有图书的处理函数
exports.queryAllBook = (req, res) => {
    // 定义查询总数 sql
    let sql1 = `SELECT COUNT(*) AS total FROM book;`;
    db.all(sql1, (err, result) => {
        if (err) return res.cc(err);
        const total = result[0].total;

        // 定义分页查询 sql
        let sql2 = 'SELECT * FROM book LIMIT ? OFFSET ?';

        db.all(sql2, [req.body.size, (req.body.currentPage - 1) * req.body.size], (err, result) => {
            if (err) return res.cc(err);

            if (result.length === 0) {
                res.send({
                    status: 1,
                    message: "图书馆还没有上书！",
                    data: result
                });
            } else {
                res.send({
                    status: 0,
                    message: "查询图书成功！",
                    data: {
                        result,
                        total: total
                    }
                });
            }
        });
    });
};

// 借阅图书方法
exports.JoinBorrowing = (req, res) => {
    console.log("Received body:", req.body); // 打印接收到的请求体
    const userinfo = req.body;
    const { codeConst, username, returntime } = req.body;
    console.log("Received parameters:", { codeConst, username, returntime });

    // 查询用户是否存在
    const sql = `SELECT * FROM user WHERE username = ?`;
    db.all(sql, [userinfo.username], (err, results) => {
        if (err) return res.cc(err.message);
        if (results.length <= 0) return res.cc('用户不存在,无法借阅书！');

        // 查询书籍是否存在
        const sql = `SELECT * FROM book WHERE bookname = ?`;
        db.all(sql, [userinfo.bookname], (err, results) => {
            if (err) return res.cc(err.message);
            if (results.length <= 0) return res.cc('书籍不存在,无法借阅书！');

            // 查询库存是否大于 1
            const sql = `SELECT inventory,id FROM book WHERE bookname = ?`;
            db.all(sql, [userinfo.bookname], (err, results) => {
                if (err) return res.cc(err.message);
                if (results.length < 1 || results[0].inventory < 1) return res.cc('库存不足,无法借阅书！');

                // 用户书籍都存在，进行借阅
                db.serialize(() => {
                    db.run('BEGIN TRANSACTION');

                    const borrowSql = `INSERT INTO borrowing(code, bookname, time, username) VALUES(?, ?, ?, ?)`;
                    const updateSql = `UPDATE book SET inventory = inventory - 1 WHERE bookname = ?`;

                    db.run(borrowSql, [results[0].id,userinfo.bookname, userinfo.time, userinfo.username], (err) => {
                        if (err) {
                            db.run('ROLLBACK');
                            return res.cc("加入借阅数据库失败！");
                        }

                        db.run(updateSql, [userinfo.bookname], (err) => {
                            if (err) {
                                db.run('ROLLBACK');
                                return res.cc("库存更新失败！");
                            }

                            db.run('COMMIT', (err) => {
                                if (err) {
                                    db.run('ROLLBACK');
                                    return res.cc(err.message);
                                }
                                res.send({
                                    status: 0,
                                    message: '借阅成功！'
                                });
                            });
                        });
                    });
                });
            });
        });
    });
};

exports.BookScanBorrowing = (req, res) => {
    const { codeConst1, username1, borrowtime1 } = req.body;

    console.log("Received parameters:", { codeConst1, username1, borrowtime1 });

    if (!codeConst1) {
        return res.cc("书籍编号不能为空！codeConst");
    }

    db.serialize(() => {
        // 查询用户是否存在
        const sqlFindUser = `SELECT * FROM user WHERE username = ?`;
        db.get(sqlFindUser, [username1], (err, user) => {
            if (err) {
                console.error("Error querying user:", err.message);
                return res.cc("查询用户信息失败！username");
            }

            if (!user) {
                return res.cc("用户不存在，无法借书！");
            }

            // 查询书籍是否存在
            const sqlFindBook = `SELECT id, bookname, inventory FROM book WHERE id = ?`;
            db.get(sqlFindBook, [codeConst1], (err, book) => {
                if (err) {
                    console.error("Error querying book:", err.message);
                    return res.cc("查询书籍信息失败！");
                }

                if (!book) {
                    return res.cc("书籍不存在，无法借书！");
                }

                if (book.inventory < 1) {
                    return res.cc("书籍库存不足，无法借书！");
                }

                // 开始事务
                db.run("BEGIN TRANSACTION", (err) => {
                    if (err) {
                        console.error("Error starting transaction:", err.message);
                        return res.cc("操作失败，无法开始事务！");
                    }

                    // 插入到 borrowing 表
                    const sqlInsertBorrowing = `INSERT INTO borrowing(code, bookname, time, username) VALUES (?, ?, ?, ?)`;
                    db.run(sqlInsertBorrowing, [codeConst1, book.bookname, borrowtime1, username1], (err) => {
                        if (err) {
                            console.error("Error inserting into borrowing:", err.message);
                            return db.run("ROLLBACK", () => res.cc("借书失败，请重试！"));
                        }

                        // 更新 book 表库存
                        const sqlUpdateBookStock = `UPDATE book SET inventory = inventory - 1 WHERE id = ?`;
                        db.run(sqlUpdateBookStock, [codeConst1], (err) => {
                            if (err) {
                                console.error("Error updating book stock:", err.message);
                                return db.run("ROLLBACK", () => res.cc("借书失败，请重试！"));
                            }

                            // 提交事务
                            db.run("COMMIT", (err) => {
                                if (err) {
                                    console.error("Error committing transaction:", err.message);
                                    return db.run("ROLLBACK", () => res.cc("借书失败，请重试！"));
                                }

                                res.send({
                                    data: {
                                        bookname: book.bookname,
                                    },
                                    status: 0,
                                    message: "借书成功！",
                                });
                            });
                        });
                    });
                });
            });
        });
    });
};


// 智能推荐图书处理函数
exports.recommendBooks = (req, res) => {
    const username = req.user.username; // 当前登录用户的 ID
    const { currentPage, size } = req.body; // 分页参数

    // 校验分页参数是否有效
    if (!currentPage || !size || currentPage < 1 || size < 1) {
        return res.cc("分页参数无效");
    }

    const offset = (currentPage - 1) * size;

    // 检查用户是否有借阅记录
    const sql1 = `SELECT bookname FROM borrowinghistory WHERE username = ?`;
    db.all(sql1, [username], (err, borrowRecords) => {
        if (err) return res.cc(err);

        const borrowedBookIds = borrowRecords.map(record => record.bookname); // 获取所有借阅过的书籍ID

        if (borrowRecords.length > 0) {
            // 如果有借阅记录，根据已借阅书籍的分类推荐，并且排除已借阅的书籍
            const sql2 = `
                SELECT DISTINCT category 
                FROM book 
                WHERE bookname IN (${borrowedBookIds.map(() => '?').join(',')})
            `;
            db.all(sql2, borrowedBookIds, (err, categories) => {
                if (err) return res.cc(err);
                const categoryList = categories.map(cat => cat.category);
                const sql3 = `
                    SELECT book.*, COUNT(borrowinghistory.bookname) AS borrow_count 
                    FROM book 
                    LEFT JOIN borrowinghistory ON book.bookname = borrowinghistory.bookname 
                    WHERE book.category IN (${categoryList.map(() => '?').join(',')}) 
                    AND book.bookname NOT IN (${borrowedBookIds.map(() => '?').join(',')}) -- 排除已借阅的书籍
                    GROUP BY book.bookname 
                    ORDER BY borrow_count DESC
                    LIMIT ? OFFSET ?
                `;
                db.all(sql3, [...categoryList, ...borrowedBookIds, size, offset], (err, books) => {
                    if (err) return res.cc(err);

                    res.send({
                        status: 0,
                        message: "推荐图书成功！",
                        data: books,
                    });
                });
            });
        } else {
            // 如果没有借阅记录，推荐借阅次数最多的书籍，并且确保这些书籍不是用户已经借阅过的
            const sql4 = `
                SELECT book.*, COUNT(borrowinghistory.bookname) AS borrow_count 
                FROM book 
                LEFT JOIN borrowinghistory ON book.bookname = borrowinghistory.bookname 
                WHERE book.id NOT IN (${borrowedBookIds.length ? borrowedBookIds.map(() => '?').join(',') : 'NULL'}) -- 排除已借阅的书籍
                GROUP BY book.id 
                ORDER BY borrow_count DESC
                LIMIT ? OFFSET ?
            `;
            db.all(sql4, [...borrowedBookIds, size, offset], (err, books) => {
                if (err) return res.cc(err);

                res.send({
                    status: 0,
                    message: "推荐图书成功！",
                    data: books,
                });
            });
        }
    });
};


// 查询个人已经借阅图书
exports.BookRturnSellct = (req, res) => {
    const userinfo = req.user;
    const sql1 = `SELECT COUNT(*) AS total FROM borrowing WHERE username = ?`;
    db.all(sql1, [req.user.username], (err, result) => {
        if (err) return res.cc(err);
        const total = result[0].total;

        const sql2 = `SELECT * FROM borrowing WHERE username = ? LIMIT ? OFFSET ?`;
        db.all(sql2, [userinfo.username, req.body.size, (req.body.currentPage - 1) * req.body.size], (err, result) => {
            if (err) return res.cc(err.message);
            if (result.length === 0) {
                res.send({
                    status: 0,
                    message: "你没有正在借阅的图书!",
                    data: result
                });
            } else {
                res.send({
                    status: 0,
                    message: '获取借阅记录成功!',
                    data: result,
                    total: total
                });
            }
        });
    });
};



exports.Returnbook = (req, res) => {
    const userinfo = req.body;
    console.log(userinfo);

    // 开始事务
    db.serialize(() => {
        db.run("BEGIN TRANSACTION;", [], (err) => {
            if (err) {
                return res.cc(err.message);
            }

            // 事务中的 SQL 操作语句
            const sql1 = `INSERT INTO borrowinghistory(bookname, time, returntime, username) VALUES(?, ?, ?, ?)`;
            const sql2 = `DELETE FROM borrowing WHERE id = ?`;

            // 事务中的第 1 个 SQL 操作
            db.run(sql1, [userinfo.bookname, userinfo.time, userinfo.returntime, userinfo.username], function (err) {
                if (err) {
                    db.run("ROLLBACK;", [], () => {
                        return res.cc(err.message);
                    });
                }

                // 事务中的第 2 个 SQL 操作
                db.run(sql2, [userinfo.id], function (err) {
                    if (err) {
                        db.run("ROLLBACK;", [], () => {
                            return res.cc(err.message);
                        });
                    }

                    // 提交事务
                    db.run("COMMIT;", [], (err) => {
                        if (err) {
                            db.run("ROLLBACK;", [], () => {
                                return res.cc(err.message);
                            });
                        }
                        res.send({
                            status: 0,
                            message: '归还成功!'
                        });
                    });
                });
            });
        });
    });
};


exports.BookScanReturn= (req, res) => {
    console.log("Received body:", req.body); // 打印接收到的请求体
    const { codeConst, username, returntime } = req.body;
    console.log("Received parameters:", { codeConst, username, returntime });

    if (!codeConst) {
        return res.cc("书籍编号不能为空！");
    }

    db.serialize(() => {
        // 查询借阅记录
        const sqlFindBorrowing = `SELECT id, code, bookname, time FROM borrowing WHERE code = ? AND username = ?`;
        db.get(sqlFindBorrowing, [codeConst, username], (err, borrowingRecord) => {
            if (err) {
                console.error("Error querying borrowing:", err.message);
                return res.cc("查询借阅记录失败！");
            }

            if (!borrowingRecord) {
                return res.cc(username);
            }

            // 开始事务
            db.run("BEGIN TRANSACTION", (err) => {
                if (err) {
                    console.error("Error starting transaction:", err.message);
                    return res.cc("操作失败，无法开始事务！");
                }

                // 插入到 borrowinghistory 表
                const sqlInsertHistory = `INSERT INTO borrowinghistory(bookname, time, returntime, username) VALUES (?, ?, ?, ?)`;
                db.run(sqlInsertHistory, [borrowingRecord.bookname, borrowingRecord.time, returntime, username], (err) => {
                    if (err) {
                        console.error("Error inserting into borrowinghistory:", err.message);
                        return db.run("ROLLBACK", () => res.cc("请重新扫码"));
                    }

                    // 删除 borrowing 表中的记录
                    const sqlDeleteBorrowing = `DELETE FROM borrowing WHERE id = ?`;
                    db.run(sqlDeleteBorrowing, [borrowingRecord.id], (err) => {
                        if (err) {
                            console.error("Error deleting from borrowing:", err.message);
                            return db.run("ROLLBACK", () => res.cc("请重新扫码"));
                        }

                        // 更新 book 表库存
                        const sqlUpdateBookStock = `UPDATE book SET inventory = inventory + 1 WHERE bookname = ?`;
                        db.run(sqlUpdateBookStock, [borrowingRecord.bookname], (err) => {
                            if (err) {
                                console.error("Error updating book stock:", err.message);
                                return db.run("ROLLBACK", () => res.cc("请重新扫码"));
                            }
                            const bookname = borrowingRecord.bookname;
                            const time = borrowingRecord.time;
                            // 提交事务
                            db.run("COMMIT", (err) => {
                                if (err) {
                                    console.error("Error committing transaction:", err.message);
                                    return db.run("ROLLBACK", () => res.cc("请重新扫码"));
                                }

                                res.send({
                                    data:{
                                        bookname,
                                        time
                                    },
                                    status: 0,
                                    message: "归还成功！",
                                });
                            });
                        });
                    });
                });
            });
        });
    });
};

// 我的借阅记录
exports.Myborrowingrecords = (req, res) => {
    const userinfo = req.body;
    const sql1 = `SELECT COUNT(*) AS total FROM borrowinghistory WHERE username = ?`;
    db.all(sql1, [req.user.username], (err, result) => {
        if (err) return res.cc(err);
        const total = result[0].total;

        const sql2 = 'SELECT * FROM borrowinghistory WHERE username = ? LIMIT ? OFFSET ?';
        db.all(sql2, [req.user.username, req.body.size, (req.body.currentPage - 1) * req.body.size], (err, result) => {
            if (err) return res.cc(err);

            if (result.length === 0) {
                res.send({
                    status: 0,
                    message: "没有借阅记录！",
                    data: result
                });
            } else {
                res.send({
                    status: 0,
                    message: "我的借阅记录查询成功！",
                    data: {
                        result,
                        total: total
                    }
                });
            }
        });
    });
};
